OpenEdge Data Management:<br />SQL Development Writing stored procedures

Use any text editor to write the CREATE PROCEDURE statement and save the source text as a text file. That way, you can easily modify the source text and try again if it generates syntax or Java compilation errors.

From the command prompt, you can invoke SQL Explorer and submit the file containing the CREATE PROCEDURE statement as an input script, as shown in Example 9–5.

Example 9–5: CREATE PROCEDURE input script
$ sqlexp -infile hello_world_script.sql example_db

From the command prompt, you can invoke SQL Explorer and submit the file containing the CREATE PROCEDURE statement as an input script, as shown in Example 9–6.

Example 9–6: CREATE PROCEDURE in context of application call
-- File name: hello_world_script.sql -- Purpose: Illustrate a CREATE PROCEDURE statement. @echo true; @autocommit true; CREATE PROCEDURE HelloWorld () BEGIN SQLIStatement Insert_HelloWorld = new SQLIStatement ( "INSERT INTO HelloWorld(fld1) values (’Hello World!’)"); Insert_HelloWorld.execute(); END ; COMMIT WORK;

Example 9–6: CREATE PROCEDURE in context of application call

-- File name: hello_world_script.sql
-- Purpose: Illustrate a CREATE PROCEDURE statement.
@echo true;
@autocommit true;
CREATE PROCEDURE HelloWorld ()
 
BEGIN
     SQLIStatement Insert_HelloWorld = new SQLIStatement (
     "INSERT INTO HelloWorld(fld1) values (’Hello World!’)");
     Insert_HelloWorld.execute();
END
;
COMMIT WORK;

The Java snippet within the CREATE PROCEDURE statement does not execute as a stand-alone program. Instead, it executes in the context of an application call to the method of the class created by the OpenEdge SQL Engine. This characteristic has the following implications:

If the snippet declares any classes, it must instantiate them within the snippet to invoke their methods.

The OpenEdge SQL Engine redirects the standard output stream to a file. This means method invocations, such as System.out.println, will not display messages on the screen, but instead write them to that file.

Invoking stored procedures

The manner in which applications call stored procedures depends on their environment.

From ODBC

Use parameter markers (question marks used as placeholders) for input or output parameters to the procedure. You can also use literal values for input parameters only. OpenEdge stored procedures do not support return values in the ODBC escape sequence.

Syntax
{ CALL proc_name [ ( parameter [ , ... ] ) ] } ;

Embed the escape sequence in an ODBC SQLExecDirect call to execute the procedure.

Example 9–7 shows a call to a stored procedure named order_parts that passes a single input parameter using a parameter marker.

Example 9–7: Stored procedure passing a single output parameter
SQLUINTEGER Part_num; SQLINTEGER Part_numInd = 0; // Bind the parameter. SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, &Part_num, 0, Part_numInd); // Place the department number in Part_num. Part_num = 318; // Execute the statement. SQLExecDirect(hstmt, "{ call order_parts(?) } ", SQL_NTS);

Example 9–7: Stored procedure passing a single output parameter

SQLUINTEGER Part_num;
SQLINTEGER  Part_numInd = 0;
 
// Bind the parameter.
     SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT,
     SQL_C_SLONG, SQL_INTEGER,
     0, 0, &Part_num, 0, Part_numInd);
 
// Place the department number in Part_num.
Part_num = 318;
// Execute the statement.
SQLExecDirect(hstmt, "{ call order_parts(?) } ", SQL_NTS);

From JDBC

Syntax
{ CALL proc_name [ ( parameter [ , ... ] ) ] } ;

Embed the escape sequence in a JDBC CallableStatement.prepareCall method invocation.

Example 9–8 shows the JDBC code parallel to the ODBC code excerpt shown in the previous example.

Example 9–8: JDBC stored procedure code
try { CallableStatement statement; int Part_num = 318; // Associate the statement with the procedure call // (conn is a previously-instantiated connection object) statement = conn.prepareCall("{call order_parts(?)}"); // Bind the parameter. statement.setInt(1, Part_num); // Execute the statement. statement.execute(); }

Example 9–8: JDBC stored procedure code

try
{
     CallableStatement statement;
     int Part_num = 318;
     
     // Associate the statement with the procedure call
     // (conn is a previously-instantiated connection object)
     statement = conn.prepareCall("{call order_parts(?)}");
     
     // Bind the parameter.
     statement.setInt(1, Part_num);
     
     // Execute the statement.
     statement.execute();
}

Modifying and deleting stored procedures

To modify a procedure, you must drop and re-create it. To re-create the procedure, you need the original source of the CREATE PROCEDURE statement. Query system tables to extract the source of the CREATE PROCEDURE statement to a file.

The SQL DROP PROCEDURE statement deletes stored procedures from the database. Exercise care in dropping procedures, since any procedure that calls the dropped procedure will raise an error condition when the now nonexistent stored procedure is invoked.

Stored procedure security

To create a stored procedure, a user must have RESOURCE or DBA privileges.

The DBA privilege entitles a user to execute any stored procedure.

The DBA privilege entitles a user to drop any stored procedure.

The owner of a stored procedure is given EXECUTE privilege on that procedure at creation time, by default.

The privileges on a procedure can be granted to another user or to public either by the owner of that procedure or by the DBA.

Stored procedures are executed with the definer’s rights, not the invoker’s. In other words, when a procedure is being executed on behalf of a user with EXECUTE privilege on that procedure, for the objects that are accessed by the procedure, the procedure owner’s privileges are checked and not the user’s. This enables a user to execute a procedure successfully even when the user does not have the privileges to directly access the objects that are accessed by the procedure, as long as the user has EXECUTE privilege on the procedure.